import pymysql
from setting import IP, PORT, DATABASE, USER, PASSWORD


def create_device_table(db_project):  # 创建设备表
    sql = f"""CREATE TABLE IF NOT EXISTS `iron_define_data` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，唯一，自增',
	`ranshaodaiwen` INT(11) NOT NULL COMMENT '燃烧带温度',
	`rongrongdaiwen` INT(11) NOT NULL COMMENT '熔融带温度',
	`shangbuwen` INT(11) NOT NULL COMMENT '上部温度',
	`xiabuwen` INT(11) NOT NULL COMMENT '下部温度',
	`lengfengya` FLOAT NOT NULL COMMENT '冷风压',
	`refengya` FLOAT NOT NULL COMMENT '热风压',
	`ludingya` FLOAT NOT NULL COMMENT '炉顶压',
	`ctime` DATETIME NOT NULL COMMENT '产生时间',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='设备总体数据'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=306
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()

def create_alarm_table(db_project):  # 创建异常数据表
    sql = f"""CREATE TABLE IF NOT EXISTS `alarm_data` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，唯一，自增',
	`error_item` VARCHAR(50) NOT NULL COMMENT '出错的设备参数名' COLLATE 'utf8mb4_unicode_ci',
	`error_value` FLOAT NOT NULL COMMENT '出错的设备参数值',
	`status` INT(11) NOT NULL DEFAULT '0' COMMENT '处理状态，0表示未处理，1表示处理完成',
	`staff_id` INT(11) NULL DEFAULT NULL COMMENT '处理人员编号',
	`ctime` DATETIME NOT NULL COMMENT '出错产生时间',
	`start_repair` DATETIME NULL DEFAULT NULL COMMENT '维修人员开始处理的时间',
	`repair_over` DATETIME NULL DEFAULT NULL COMMENT '错误修正时间',
	`extent` INT(11) NOT NULL COMMENT '严重程度，1表示轻微，2表示较为严重，3表示很严重',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='异常数据表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=264
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()

def create_staffs_table(db_project):  # 创建人员信息表
    sql = f"""CREATE TABLE IF NOT EXISTS `staffs` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号，主键',
	`name` VARCHAR(50) NOT NULL COMMENT '用户名' COLLATE 'utf8_general_ci',
	`password` CHAR(32) NOT NULL COMMENT 'md5加密后的密码' COLLATE 'utf8_general_ci',
	`phonenumber` VARCHAR(50) NOT NULL COMMENT '手机号' COLLATE 'utf8_general_ci',
	`email` VARCHAR(30) NOT NULL COMMENT '邮箱' COLLATE 'utf8_general_ci',
	`job_id` INT(11) NULL DEFAULT NULL COMMENT '职位',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='人员信息表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=5
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()

def create_session_table(db_project):  # 创建会话表
    sql = f"""CREATE TABLE IF NOT EXISTS `session` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '会话id，主键唯一自增',
	`staff_id` INT(11) NOT NULL COMMENT '用户id，与user表id一样',
	`value` INT(11) NOT NULL COMMENT '随机cookie值，整数，范围在1~100',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='会话管理表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=1
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()

def create_job_table(db_project):  # 创建职位表
    sql = f"""CREATE TABLE IF NOT EXISTS `job` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '职位编号，唯一',
	`name` VARCHAR(50) NOT NULL COMMENT '职位名称' COLLATE 'utf8_general_ci',
	`permissions` VARCHAR(50) NOT NULL COMMENT '权限列表' COLLATE 'utf8_general_ci',
	`description` VARCHAR(100) NOT NULL COMMENT '职位描述' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='职位等级表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()

def create_permission_table(db_project):  # 创建权限表
    sql = f"""CREATE TABLE IF NOT EXISTS `permission` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '权限编号，唯一',
	`name` VARCHAR(50) NOT NULL COMMENT '权限名称' COLLATE 'utf8_general_ci',
	`description` VARCHAR(100) NOT NULL COMMENT '权限描述' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='权限说明表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;"""
    db_project.cur.execute(sql)
    db_project.db.commit()


class DB():  # 数据库类，封装了与数据库交互的方法
    def __init__(self):  # 初始化数据库连接，同时创建游标，指定使用的数据库，并创建项目需要的数据表
        self.db = pymysql.connect(host=IP, port=PORT, user=USER, password=PASSWORD, charset='utf8mb4')
        self.cur = self.db.cursor()
        self.cur.execute(F"use {DATABASE};")
        create_device_table(self)
        create_alarm_table(self)
        create_staffs_table(self)
        create_session_table(self)
        create_permission_table(self)
        create_job_table(self)

    def __del__(self):  # 关闭游标和数据库连接
        self.cur.close()
        self.db.close()

    def write_data(self, dic, table_name):  # 通用的数据库写入方法
        keys = ','.join([i for i in dic.keys()])
        values = ','.join(['%s'] * len(dic))
        sql = f'insert into {table_name}({keys}) values({values})'
        self.cur.execute(sql, tuple(dic.values()))
        self.db.commit()

    def read_data(self, table_name):  # 通用的数据库读取方法
        self.cur.execute(f"select * from {table_name};")
        data = self.cur.fetchall()
        self.db.commit()
        return data

    def read_staffs_data(self):  # 读取员工信息
        self.cur.execute(f"select staffs.id, staffs.name, phonenumber, email, description \
from staffs left outer join job on staffs.job_id=job.id;")
        data = self.cur.fetchall()
        self.db.commit()
        return data

    def read_last_data(self, table_name):  # 读取表格名称为table_name的表的最后一条数据
        self.cur.execute(f'select count(*)-1 as count from {table_name};')
        count = self.cur.fetchone()[0]
        self.cur.execute(f"select * from {table_name} limit {count}, 1;")
        data = self.cur.fetchone()
        self.db.commit()
        return data

    def read_staffs_from_sess(self, session_id):  # 读取session_id对应的员工信息
        self.cur.execute(f"select staff_id from session where value={session_id}")
        staff_id = self.cur.fetchone()[0]
        self.cur.execute(f"select staffs.id, staffs.name, phonenumber, email, permissions \
from staffs inner join job on staffs.job_id=job.id where staffs.id={staff_id}")
        data = self.cur.fetchone()
        self.db.commit()
        return data

    def update_alarm_from_id(self, alarm_id, staff_id=None):  # 用staff_id更新alarm_id对应的告警信息，或者只更新alarm_id对应的告警信息
        if staff_id:
            sql = f"""update alarm_data set start_repair=NOW(), staff_id={staff_id} where id={alarm_id};"""
            self.cur.execute(sql)
            self.db.commit()
        else:
            sql = f"""update alarm_data set repair_over=NOW(), `status`=1 where id={alarm_id};"""
            self.cur.execute(sql)
            self.db.commit()

    def assign_job_from_id(self, staff_id, job_id):  # 用job_id更新staff_id对应的员工信息
        sql = f"""update staffs set job_id={job_id} where id={staff_id};"""
        self.cur.execute(sql)
        self.db.commit()
        return

    def del_staff_from_id(self, staff_id):  # 用staff_id删除对应的员工信息
        sql = f"""delete from staffs where id={staff_id};"""
        self.cur.execute(sql)
        self.db.commit()
        return

    def del_session(self, session_id):  # 用session_id删除对应的session信息
        sql = f"""delete from session where value={session_id};"""
        self.cur.execute(sql)
        self.db.commit()
        return

if __name__ == '__main__':
    pass



